import numpy as np
import pandas as pd
from datetime import date, timedelta


class Week_data:
    '''
    流失周报处理。
    '''

    def __init__(self, address_last_week, address_this_week, address_hua, address_tai):
        self.address_last_week = address_last_week
        self.address_this_week = address_this_week
        self.address_hua = address_hua
        self.address_tai = address_tai

    def read(self):
        print("正在读取数据...")
        # 读取花名册、上周周报
        df_hua = pd.read_csv(self.address_hua, encoding='gbk', low_memory=False)  # 字符为gbk
        df_last_week_zai = pd.read_excel(self.address_last_week, sheet_name='在职')
        # 读取ssc侧数据
        excel_reader = pd.ExcelFile(self.address_this_week)
        df_zai = excel_reader.parse('在职')
        df_ru = excel_reader.parse('入职')
        df_li = excel_reader.parse('离职')
        # 读取台账信息
        df_tai = pd.read_excel(self.address_tai, sheet_name='全职')
        # 获取成都分在、入数据
        cd_zai = df_zai[df_zai['三级部门'] == '成都分中心']
        cd_ru = df_ru[df_ru['三级部门'] == '成都分中心']
        return df_hua,df_last_week_zai,df_li,cd_zai,cd_ru,df_tai

    def out_zai(self, cd_zai, df_hua):
        print("正在处理在职信息...")
        df_hua_jian = df_hua[['erp账号','条线','汇报对象erp','汇报对象姓名','五级部门','六级部门']]
        # print(df_hua_jian)
        cd_zai_hua = pd.merge(cd_zai, df_hua_jian, how = 'left', left_on='ERP账号', right_on='erp账号')
        return cd_zai_hua

    def out_ru_liucun(self, cd_ru, df_tai):
        print("正在处理新增新人留存基本信息...")
        df_ru_liucun = cd_ru[['四级部门','五级部门','ERP账号','员工姓名','性别','职级描述','用工性质','电话','最高教育程度','最新雇佣时间','是否二次入职']]
        df_ru_liucun = df_ru_liucun[df_ru_liucun['用工性质'].isin(['A-全日制劳动合同工','D-普通实习生'])]
        tai_ru_liucun = df_tai[['ERP','渠道类别','渠道明细','专业类别','是否有过客服工作经历','最终面试结果']]
        df_ru_liucun = pd.merge(df_ru_liucun, tai_ru_liucun, how='left', left_on='ERP账号', right_on='ERP')
        return df_ru_liucun

    def out_li(self, df_li, df_last_week_zai):
        print("正在处理离职信息...")
        # 输出仅成都
        # 离职
        bumen = df_li['机构全称'].str.split('-',expand=True)
        # print(bumen)
        df_li['集团'] = bumen[0]
        df_li['零级部门'] = bumen[1]
        df_li['一级部门'] = bumen[2]
        df_li['二级部门'] = bumen[3]
        df_li['三级部门'] = bumen[4]
        df_li['四级部门'] = bumen[5]
        df_li['五级部门'] = bumen[6]
        cd_li = df_li[df_li['三级部门'] == '成都分中心']
        cd_li['职级序列'] = cd_li['职级描述'].apply(lambda x: x[0]+'序列')
        def new_old(y):
            if y < 90:
                return "新人"
            else:
                return "老人"
        cd_li['新老人'] = cd_li['司龄-天'].apply(new_old)
        # 匹配上周的周报，确定mod侧信息
        last_week_hua = df_last_week_zai[['ERP账号','MOD条线','汇报对象ERP','汇报对象','MOD五级部门','MOD六级部门']]
        cd_li = pd.merge(cd_li, last_week_hua, how = 'left', on = 'ERP账号')
        return cd_li

    def out_cd(self, cd_zai_hua, cd_ru, cd_li, df_ru_liucun):
        print("正在输出成都分中心周报...")
        excel_writer_cd = pd.ExcelWriter(address_out_cd)  # 定义writer，选择文件（文件可以不存在）
        cd_zai_hua.to_excel(excel_writer_cd, sheet_name='在职', index=False)
        cd_ru.to_excel(excel_writer_cd, sheet_name='入职', index=False)
        cd_li.to_excel(excel_writer_cd, sheet_name='离职', index=False)
        df_ru_liucun.to_excel(excel_writer_cd, sheet_name='新人留存数据源', index=False)
        excel_writer_cd.save()
        return excel_writer_cd

    def out_hr(self, cd_zai,cd_ru):
        print("正在处理人资周报...")
        # 输出人资周报
        hr_zai = cd_zai[['员工ID','ERP账号','员工姓名','用工性质','人员状态','最新雇佣时间','证件号码','电话','最高教育程度','是否二次入职','所属体系','试用期结束日期','三级部门','四级部门','五级部门']]
        hr_ru = cd_ru[['员工ID','ERP账号','员工姓名','用工性质','人员状态','最新雇佣时间','证件号码','电话','最高教育程度','是否二次入职','所属体系','试用期结束日期','三级部门','四级部门','五级部门']]
        excel_writer_hr = pd.ExcelWriter(address_out_hr)  # 定义writer，选择文件（文件可以不存在）
        hr_zai.to_excel(excel_writer_hr, sheet_name='在职', index=False)
        hr_ru.to_excel(excel_writer_hr, sheet_name='入职', index=False)
        excel_writer_hr.save()
        return excel_writer_hr

if __name__ == '__main__':
    print("the main func is run")
    # ssc周报、业务花名册、上周在入理地址（需每周修改）
    address_this_week = r'D:\系统桌面(勿删)\Desktop\JD_work\周报output\数据源\2022年第1周人员信息周报（12.31-1.6）.xlsx'
    address_hua = 'D:/系统桌面(勿删)/Desktop/JD_work/周报output/数据源/人资_线上花名册每日数据推送_d_20220106085958.csv'
    address_last_week = r'D:\系统桌面(勿删)\Desktop\JD_work\周报output\数据源\2021年第52周人员信息周报（12.24-12.30）在入离.xlsx'
    address_tai = 'D:/系统桌面(勿删)/Desktop/JD_work/招聘数据管理/台账/总台账/2021/一线客服面试总台账2021.12.31.xlsx'
    # 创建week_data对象
    week_data = Week_data(address_last_week, address_this_week, address_hua, address_tai)
    df_hua,df_last_week_zai,df_li,cd_zai,cd_ru,df_tai = week_data.read()
    # 导出地址
    yestoday = (date.today() + timedelta(days=-1)).strftime("%Y-%m-%d")
    address_out_cd = 'D:/系统桌面(勿删)/Desktop/JD_work/周报output/ssc周报仅成都' + yestoday + '.xlsx'
    address_out_hr = 'D:/系统桌面(勿删)/Desktop/JD_work/周报output/人资周报' + yestoday + '.xlsx'
    # 获取离职数据（匹配花名册）
    cd_li = week_data.out_li(df_li, df_last_week_zai)
    # 获取匹配了花名册的在职信息
    cd_zai_hua = week_data.out_zai(cd_zai, df_hua)
    # 获取新人留存表数据源
    df_ru_liucun = week_data.out_ru_liucun(cd_ru, df_tai)
    # 导出初版周报
    week_data.out_cd(cd_zai_hua, cd_ru, cd_li, df_ru_liucun)
    week_data.out_hr(cd_zai,cd_ru)
    print('导出成功!')